<?php
namespace app\manage\controller;
use think\Controller;
use think\Db;

class Show extends Controller
{
    public function index(){
        echo '外部控制器';
    }

    //数据迁移，数据统计
    public function dataBuffer(){
        $date = input('date');
        $secret = input('secret');

        if( $secret !== 'qt5iorev42rk*^' ){
            $this->layJson(1, '密钥错误');
        }

//        $date = '2018/01/01 - 2018/01/01';
        if(!empty($date)){
            $arr = explodeTime($date);
            $startTime = $arr['start_time'];
            $endTime = $arr['end_time'];
        }else{
            //默认每天2:00更新昨天的内容
            $startTime = date("Y-m-d",strtotime("-1 day"));
            $endTime = date("Y-m-d");
            $startTime = strtotime($startTime);
            $endTime = strtotime($endTime)-1;
        }

        //验证是否是同个月
        if(date('Ym', $startTime) !== date('Ym', $endTime)){
            $this->layJson(1, '时间不是同个月');
        }

        //如果已经存在该数据，则删除
        $whereDate['daytime'] = ['between', [$startTime,$endTime]];
        Db::name('DataBuffer')->where($whereDate)->delete();

        /////////////////
        $year = date('Y', $startTime);
        $month = date('Ym', $endTime);

        $wherePlat = "reg_time between {$startTime} AND {$endTime}";
        $whereAdd = "bond_time between {$startTime} AND {$endTime}"; //新增条件
        $whereRole = "create_time between {$startTime} AND {$endTime}"; //创角条件
        $whereActive = "in_time between {$startTime} AND {$endTime}"; //活跃条件
        $whereOrder = "pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1"; //订单条件

        //平台新增
        $plat = Db::name('User')
            ->field("FROM_UNIXTIME(reg_time,'%Y-%m-%d') AS daytime, channel_id, reg_cps_id as cps_id, reg_game_id as game_id, count(*) AS plat_add")
            ->where($wherePlat)
            ->group("daytime, channel_id, reg_cps_id, game_id")
            ->order('daytime desc')
            ->select();

        //游戏新增
        $add = Db::name('GameUserY_'.$year)
            ->field("FROM_UNIXTIME(bond_time,'%Y-%m-%d') AS daytime, channel_id, cps_id, game_id, count(*) AS game_add")
            ->where($whereAdd)
            ->group("daytime, channel_id, cps_id, game_id")
            ->order('daytime desc')
            ->select();

        //玩家创建的角色
        $role = Db::name('GameUserRoleY_'.$year)
            ->field("FROM_UNIXTIME(create_time,'%Y-%m-%d') AS daytime,channel_id, cps_id, game_id, count(*) AS game_role")
            ->where($whereRole)
            ->group("daytime, channel_id, cps_id, game_id")
            ->order('daytime desc')
            ->select();

        //游戏活跃
        $active = Db::name('GameUserLogM_'.$month)
            ->field("FROM_UNIXTIME(in_time,'%Y-%m-%d') AS daytime, channel_id, cps_id, game_id, count(DISTINCT(my_user_id)) AS game_active")
            ->where($whereActive)
            ->group("daytime, channel_id, cps_id, game_id")
            ->order('daytime desc')
            ->select();

        $order = Db::name('PayOrderY_'.$year)
            ->field("FROM_UNIXTIME(pay_time,'%Y-%m-%d') AS daytime, channel_id, reg_cps_id AS cps_id, game_id, count(DISTINCT(my_user_id)) AS pay_number,sum(total_price) AS pay_money")
            ->where($whereOrder)
            ->group("daytime, channel_id, reg_cps_id, game_id")
            ->order('daytime desc')
            ->select();

        //统计总数组
        $allData = [];
        foreach($plat as $k=>$v){
            $allData[$v['daytime']][$v['channel_id']][$v['cps_id']][$v['game_id']]['plat_add'] = $v['plat_add'];
        }
        foreach($add as $k=>$v){
            $allData[$v['daytime']][$v['channel_id']][$v['cps_id']][$v['game_id']]['game_add'] = $v['game_add'];
        }
        foreach($role as $k=>$v){
            $allData[$v['daytime']][$v['channel_id']][$v['cps_id']][$v['game_id']]['game_role'] = $v['game_role'];
        }
        foreach($active as $k=>$v){
            $allData[$v['daytime']][$v['channel_id']][$v['cps_id']][$v['game_id']]['game_active'] = $v['game_active'];
        }
        foreach($order as $k=>$v){
            $allData[$v['daytime']][$v['channel_id']][$v['cps_id']][$v['game_id']]['pay_number'] = $v['pay_number'];
            $allData[$v['daytime']][$v['channel_id']][$v['cps_id']][$v['game_id']]['pay_money'] = $v['pay_money'];
        }

        if(!$allData){
            $this->layJson(1, '数据为空');
        }

        //合并转二维数组
        $twoData = [];

        $gameIdName = model('manage/Game')->gameIdName();
        $channelIdName = model('manage/Channel')->channelIdName();
        foreach ($allData as $daytime=>$v1){
            foreach ($v1 as $channelId=>$v2){
                foreach ($v2 as $cpsId=>$v3){
                    foreach ($v3 as $gameId=>$v4){
                        $platAdd = isset($v4['plat_add']) ? $v4['plat_add'] : 0;
                        $gameAdd = isset($v4['game_add']) ? $v4['game_add'] : 0;
                        $gameRole = isset($v4['game_role']) ? $v4['game_role'] : 0;
                        $gameActive = isset($v4['game_active']) ? $v4['game_active'] : 0;
                        $payNumber = isset($v4['pay_number']) ? $v4['pay_number'] : 0;
                        $payMoney = isset($v4['pay_money']) ? $v4['pay_money'] : 0;

                        if($channelId==1000){
                            $cpsName = isset($channelIdName[ $cpsId ]) ? $channelIdName[ $cpsId ] : '';
                        }else{
                            $cpsName = isset($channelIdName[ $channelId ]) ? $channelIdName[ $channelId ] : '';
                        }

                        $twoData[] = [
                            'daytime' => strtotime($daytime),
                            'channel_id' => $channelId,
                            'cps_id' => $cpsId,
                            'cps_name' => $cpsName,
                            'game_id' => $gameId,
                            'game_name' => isset($gameIdName[ $gameId ]) ? $gameIdName[ $gameId ] : '',
                            'plat_add' => $platAdd,
                            'game_add' => $gameAdd,
                            'game_role' => $gameRole,
                            'game_active' => $gameActive,
                            'pay_number' => $payNumber,
                            'pay_money' => round($payMoney/100, 2),
                            'pay_rate' => $gameActive ? round($payNumber/$gameActive*100, 2).'%' : '0%',
                            'arpu' => $gameAdd ? round($payMoney/$gameAdd, 2) : 0,
                            'arppu' => $gameActive ? round($payMoney/$gameActive, 2) : 0,
                            'arpppu' => $payNumber ? round($payMoney/$payNumber, 2) : 0
                        ];
                    }
                }
            }
        }

        //写入数据库,每次添加500条
        $page = ceil(count($twoData)/500);
        for($i=0; $i<$page; $i++){
            $sliceData = array_slice($twoData, $i*500, 500);
            Db::name('DataBuffer')->insertAll($sliceData);
        }

        $this->layJson(0, '数据缓存成功');
    }

    //修改账单状态，每周二早上10点过滤掉状态为1的
    public function autoExpire(){
        if(input('secret') !== 'qt5iorev42rk*^') exit;

        $systemTime = model('CommonOption')->where('id', 200)->value('value');

        if(time() >= $systemTime){
            model('CpsBill')->save(['status'=>4], ['status'=>1]);

            $w = date('w'); //2
            $a = (7-$w)*86400; //5天的时间
            $s = strtotime(date('Y-m-d'))+$a+86400*2+3600*11; //今天的时间戳加上4天的时间戳+周日周一的时间戳+周二的11个小时=下周二的11点
            model('CommonOption')->save(['value'=>$s], ['id'=>200]);
            echo '成功';
        }
    }

    //导入
    public function import(){
        exit;
        import("php_excel.PHPExcel");
        //文件名为文件路径和文件名的拼接字符串
        $objReader = \PHPExcel_IOFactory::createReader('Excel2007');//创建读取实例
        /*
         * log()//方法参数
         * $file_name excal文件的保存路径
         */
        $objPHPExcel = $objReader->load('D:\01.xlsx',$encode='utf-8');//加载文件
        $sheet = $objPHPExcel->getSheet(0);//取得sheet(0)表
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数
        for($i=1; $i<=$highestRow; $i++){
            for($j='A'; $j<=$highestColumn; $j++){
                $cellValue = $objPHPExcel->getActiveSheet()->getCell($j.$i)->getValue();
                if($cellValue instanceof \PHPExcel_RichText){   //富文本转换字符串
                    $cellValue = $cellValue->__toString();
                }
                $data[$i][] = $cellValue;
            }
        }

        $data2 = [];
        foreach ($data as $k=>$v){
            if($v[1] == ' '){
                continue;
            }
            $v[1] = round($v[1], 0);
            $data2[] = $v;
        }

        $objPHPExcel = $objReader->load('D:\02.xlsx',$encode='utf-8');//加载文件
        $sheet = $objPHPExcel->getSheet(0);//取得sheet(0)表
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数
        for($i=1; $i<=$highestRow; $i++){
            for($j='A'; $j<=$highestColumn; $j++){
                $cellValue = $objPHPExcel->getActiveSheet()->getCell($j.$i)->getValue();
                if($cellValue instanceof \PHPExcel_RichText){   //富文本转换字符串
                    $cellValue = $cellValue->__toString();
                }
                $data3[$i][] = $cellValue;
            }
        }

        $data4 = array_values($data3);
    }

    //登录
    public function login(){
        if(request()->isAjax()){
            $where['account'] = input('account');
            $where['password'] = md5(input('password'));
            if( ! $find = model('SystemAdmin')->where($where)->find() ){
                $this->layJson(1, '账号或者密码错误');
            }

            if($find['status'] == 0){
                $this->layJson(1, '该账号被禁用');
            }

            $update['id'] = $find['id'];
            $update['lasttime'] = time();
            $update['lastip'] = request()->ip();
            model('SystemAdmin')->isUpdate(true)->save($update);

            session('admin.id', $find['id']);
            session('admin.role_id', $find['roleid']);
            session('admin.account', $find['account']);
            session('admin.password', $find['password']);
            session('admin.remark', $find['remark']);
            session('admin.lock', 'miyuhaiwai');
            session('admin.start_time', time());

            $this->layJson(0, '登录中....');
        }else{
            return view();
        }
    }

    protected function layJson($code=0, $msg='', $data=[], $count=0){
        $json['code'] = $code;
        $json['msg'] = $msg;
        $json['data'] = $data;
        $json['count'] = $count;
        echo json_encode($json);exit;
    }
}